﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SMSModels;
using System.Data;
using System.Data.SQLite;

namespace DAL
{
    public class GoodsFilesService
    {

        /// <summary>
        /// 添加货物档案
        /// </summary>
        /// <param name="goods">goods对象</param>
        /// <returns>返回受影响行数</returns>
        public int AddGoodsFile(GoodsFiles goodsFiles)
        {
            //[1]封装SQL语句，添加货物档案
            String sql = String.Format("insert into 货物档案表 (货物编号,货物名称,供货商ID,规格,计量单位,单价,备注) values('{0}','{1}',{2},'{3}','{4}',{5},'{6}')", goodsFiles.GoodsID, goodsFiles.GoodsName, goodsFiles.SupplierID, goodsFiles.Spec, goodsFiles.Units, goodsFiles.Monovalent, goodsFiles.Remarks);
            String sqlStrIsExist = String.Format("select * from 货物档案表 where 货物编号='{0}'", goodsFiles.GoodsName);

            //[2]提交插入
            if (SQLHelper.GetReader(sqlStrIsExist).HasRows)
            {
                return -1;

            }
            else
            {
                return SQLHelper.Update(sql);
            }

        }

        /// <summary>
        /// 删除货物档案
        /// </summary>
        /// <param name="goods">goods对象</param>
        /// <returns>返回受影响行数</returns>
        public int DeleteGoodsFiles(GoodsFiles goodsFiles)
        {
            //[1]封装SQL语句，删除货物档案
            String sql = String.Format("delete from 货物档案表 where 货物编号='{0}'", goodsFiles.GoodsID);

            //[2]提交插入
            return SQLHelper.Update(sql);

        }


        /// <summary>
        /// 修改货物档案信息,不能修改编号
        /// </summary>
        /// <param name="goods">goods对象</param>
        /// <returns>返回受影响行数</returns>
        public int UpdateGoodsFilesInfo(GoodsFiles goodsFiles)
        {
            //[1]封装SQL语句，修改货物档案信息
            String sql = String.Format("update 货物档案表 set 货物名称='{0}',供货商ID={1}, 规格='{2}', 计量单位='{3}', 单价={4}, 备注='{5}' where 货物编号='{6}'", goodsFiles.GoodsName, goodsFiles.SupplierID, goodsFiles.Spec, goodsFiles.Units, goodsFiles.Monovalent, goodsFiles.Remarks, goodsFiles.GoodsID);

            //[2]提交修改
            return SQLHelper.Update(sql);


        }

        /// <summary>
        /// 获取货物档案信息数据集（dataset）
        /// </summary>
        /// <returns>返回dataset对象</returns>
        public DataSet GetDataSet()
        {
            String sql = "select 货物编号,货物名称,供货商信息表.供货商名称,规格,计量单位,单价,货物档案表.备注 from 货物档案表,供货商信息表 where 货物档案表.供货商ID=供货商信息表.供货商ID";
            String tbName = "goodsFilesInfo";
            return SQLHelper.GetDataSet(sql, tbName);
        }

        /// <summary>
        /// 获取货物档案list集合
        /// </summary>
        /// <returns>返回货物档案list集合</returns>
        public List<GoodsFiles> GetGoodsInfo()
        {
            String sql = "select 货物编号,货物名称,货物档案表.供货商ID,供货商名称,规格,计量单位,单价,货物档案表.备注 from 货物档案表,供货商信息表 where 货物档案表.供货商ID=供货商信息表.供货商ID";
            SQLiteDataReader reader = SQLHelper.GetReader(sql);
            List<GoodsFiles> list = new List<GoodsFiles>();
            while (reader.Read())
            {
                list.Add(new GoodsFiles
                {

                    GoodsID = reader["货物编号"].ToString(),
                    GoodsName = reader["货物名称"].ToString(),
                    SupplierID = Convert.ToInt32(reader["供货商ID"]),
                    SupplierName=reader["供货商名称"].ToString(),
                    Spec = reader["规格"].ToString(),
                    Units = reader["计量单位"].ToString(),
                    Monovalent = Convert.ToDouble(reader["单价"]),
                });
            }
            reader.Close();
            return list;
        }


        /// <summary>
        /// 获取货物信息，货物编号组合框选择触发方法
        /// </summary>
        /// <param name="goodsID"></param>
        /// <returns></returns>
        public List<GoodsFiles> GetListOnSelectGoodsNum(String goodsID)
        {
            String sql = String.Format("select 货物名称,规格,计量单位,单价 from 货物档案表 where 货物编号='{0}'", goodsID);
            SQLiteDataReader reader = SQLHelper.GetReader(sql);
            List<GoodsFiles> list = new List<GoodsFiles>();
            while (reader.Read())
            {
                list.Add(new GoodsFiles
                {
                    GoodsID = reader["货物名称"].ToString(),
                    Spec = reader["规格"].ToString(),
                    Units = reader["计量单位"].ToString(),
                    Monovalent = Convert.ToDouble(reader["单价"]),

                });
            }

            return list;

        }


        /// <summary>
        /// 货物编号是否存在
        /// </summary>
        /// <param name="goodsID"></param>
        /// <returns>存在返回true，否则返回false</returns>
        public bool GoodsIDIsExist(string goodsID)
        {
            string sql = string.Format("select * from 货物档案表 where 货物编号='{0}'",goodsID);
            SQLiteDataReader rd = SQLHelper.GetReader(sql);
            if (rd.HasRows)
            {
                rd.Close();
                return true;
            }
            else
            {
                rd.Close();
                return false;
            }
        }

    }
}
